﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using Maticsoft.DBUtility;

namespace ExtendBLL
{
    public class Score
    {
        /// <summary>
        /// 获取成绩信息
        /// </summary>
        /// <param name="ClassID"></param>
        /// <param name="CourseID"></param>
        /// <returns></returns>
        public DataSet GetScoreInfo(int ClassID, int ProjectCourseID)
        {
            StringBuilder strBuild=new StringBuilder();
            strBuild.Append("select distinct a.[ID] as StudentID,a.Student_Code,a.Student_Name,b.* from Student a left join (select * from Score where ProjectCourseID=" + ProjectCourseID + ") as b on a.[ID]=b.StudentID where a.ClassID=" + ClassID + " order by a.[ID] Asc");

            return DbHelperSQL.Query(strBuild.ToString());
        }

        /// <summary>
        /// 获取项目信息
        /// </summary>
        /// <param name="CourseID">课程ID</param>
        /// <returns></returns>
        public DataSet GetSubjectInfoByCourseID(int CourseID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select distinct a.[CourseID], b.Project_Name,b.[ID] from CourseAndProject a, Project b where a.ProjectID=b.[ID] and a.CourseID="+CourseID);

            return DbHelperSQL.Query(strSql.ToString());
        }

        public void AddScore(int[] iarray, List<LabMS.Model.Score> list)
        {
            StringBuilder strSql = new StringBuilder();
            string str;
            for (int i = 0; i < list.Count; i++)
            {
                if (iarray[i] == 0)
                {
                    strSql.Append(" insert Score(");
                    if(list[i].MScore.ToString() != "")
                    {
                        strSql.Append("MScore,");
                    }
                    strSql.Append("ClassID,StudentID,CourseID,StudentName,StudentCode,CourseName,");
                    if(list[i].Score_Op.ToString() != "")
                    {
                         strSql.Append("Score_Op,");
                    }
                    if (list[i].Score_Pre.ToString() != "")
                    {
                        strSql.Append("Score_Pre,");
                    }
                    if (list[i].Score_Ex.ToString() != "")
                    {
                        strSql.Append("Score_Ex,");
                    }
                    strSql.Append("Score_Percent,ProjectCourseID,ClassItemID) values(");
                    if(list[i].MScore.ToString() != "")
                    {
                        strSql.Append(int.Parse(list[i].MScore.ToString())+",");
                    }
                    strSql.Append(int.Parse(list[i].ClassID.ToString())+","+int.Parse(list[i].StudentID.ToString())+","+int.Parse(list[i].CourseID.ToString()) + ",'"+ list[i].StudentName + "','"+ list[i].StudentCode + "','" + list[i].CourseName + "',");
                    if(list[i].Score_Op.ToString() != "")
                    {
                        strSql.Append(int.Parse(list[i].Score_Op.ToString()) + ",");
                    }
                    if (list[i].Score_Pre.ToString() != "")
                    {
                        strSql.Append(int.Parse(list[i].Score_Pre.ToString()) + ",");
                    }
                    if (list[i].Score_Ex.ToString() != "")
                    {
                        strSql.Append(int.Parse(list[i].Score_Ex.ToString()) + ",");
                    }
                    strSql.Append("'"+list[i].Score_Percent + "'," + int.Parse(list[i].ProjectCourseID.ToString()) +","+int.Parse(list[i].ClassItemID.ToString())+ ") ");
                }
                else if (iarray[i] == 1)
                {
                    StringBuilder strSql2 = new StringBuilder();
                    //if (String.IsNullOrEmpty(list[i].MScore.ToString()) && String.IsNullOrEmpty(list[i].Score_Op.ToString()) && String.IsNullOrEmpty(list[i].Score_Pre.ToString()) && String.IsNullOrEmpty(list[i].Score_Ex.ToString()))
                    //{
                    //    return;
                    //}
                    //else
                    //{
                        strSql2.Append(" update Score set ");
                        if (list[i].MScore.ToString() != "")
                        {
                            strSql2.Append(" MScore=" + int.Parse(list[i].MScore.ToString()) + ",");
                        }
                        else
                        {
                            strSql2.Append(" MScore=null,");
                        }
                        if (list[i].Score_Op.ToString() != "")
                        {
                            strSql2.Append("Score_Op=" + int.Parse(list[i].Score_Op.ToString()) + ",");
                        }
                        else
                        {
                            strSql2.Append("Score_Op=null,");
                        }
                        if (list[i].Score_Pre.ToString() != "")
                        {
                            strSql2.Append("Score_Pre=" + int.Parse(list[i].Score_Pre.ToString()) + ",");
                        }
                        else
                        {
                            strSql2.Append("Score_Pre=null,");
                        }
                        if (list[i].Score_Ex.ToString() != "")
                        {
                            strSql2.Append("Score_Ex=" + int.Parse(list[i].Score_Ex.ToString()) + ",");
                        }
                        else
                        {
                            strSql2.Append("Score_Ex=null,");
                        }
                        str = strSql2.ToString().Substring(0, strSql2.ToString().Length-1);
                        strSql.Append(str + " where [ID]=" + int.Parse(list[i].ID.ToString()));
                    //}
                }

            }
            DbHelperSQL.ExecuteReader(strSql.ToString());
        }

        public DataSet ScoreQueryByStudentCode(string StudentCode,string Year,string Season)
        {
            string sql = "select * from Score,Project,CourseAndProject where Score.ProjectCourseID=CourseAndProject.[ID] and CourseAndProject.ProjectID=Project.[ID] and StudentCode='" + StudentCode + "' and Score.CourseID in (select [ID] from Course where Course.CourseYear='" + Year + "' and Course.CourseSeason='" + Season + "' )";
            return DbHelperSQL.Query(sql);
        }
    }
}
